1. Field of the Invention
The present invention relates to a system and method for viewing and updating tables of a relational database and, in particular, to a system and method for creating customizable views of a relational database so that the database users may view and update select subsets of the database tables in a manner that requires no knowledge of the underlying database schema or database commands.
2. Related Art
A relational database is database that consists entirely of tables. A table is a collection of rows, where each row is comprised of one or more fields. The rows of a table are also called records and the fields of a row are also called columns. An example relational database is illustrated in FIG. 1. The relational database of FIG. 1 is a database that could be used by any business organization that wants to keep track of employee and department data.
As can be seen from FIG. 1, the database contains two tables: an Employee table, which is used for storing useful information concerning all of the employees in the organization; and a Department table. Each record (i.e., row) in the Employee table contains information about a particular employee. These records will be called "employee records." As can be seen from FIG. 1, each employee record contains five fields of employee information. These fields are: employee id, employee name, title, salary, and department id. Similarly, each record in the Department table contains information about a particular department. For example, the first record in the Department table contains information concerning the Engineering department. Each department record contains four fields. These fields are: department id, department name, manager, budget, and number of employees.
Records in different tables of a relational database may be logically linked by what is called a "lookup field." A lookup field (also sometimes called a foreign key) is a field that is common to two or more tables and is a primary key in one of the tables. The table that has the lookup field as its primary key is called the lookup table.
In the example of FIG. 1, the department.sub.-- id field is a lookup field since it is common to both the employee table and department table, and it is a primary key in the department table. The reason it is called a lookup field is that the value that is stored in the department.sub.-- id field of an Employee record does not contain information that is immediately meaningful to a user, rather the department.sub.-- id field of an Employee record contains a value that allows a user to perform a "lookup" in the Department table so that the user may retrieve information that does have meaning to the user.
This is best understood by example. Consider a user that wants to determine the department that a particular employee works in; that user would first access the Employee table to retrieve the employee record for that particular employee. Unfortunately for that user, the employee record does not contain the name of the department the employee works in, rather the employee record merely contains a department ID, which is stored in the department.sub.-- id field. Consequently, for the user to determine the name of the department, the user would perform a "lookup" in the Department table (i.e., the lookup table) for a department record that contains a department ID value that matches the department ID value from the employee record. Once a matching record is found, the user can retrieve the matching department record to get the name of the department that is associated with the department ID stored in the employee table from the department.sub.-- name field of the Department table. The department.sub.-- name field of the Department table is called the "display field", since it contains the information that the user would prefer to have displayed. In other words, the department.sub.-- name field contains the actual name of a department as opposed to merely an integer value used for uniquely identifying a department record within the Department table, which is what is stored in the department.sub.-- id field. Lookup fields, such as the department.sub.-- id field, are useful in that they allow all of the information relating to a department to exist in the department table, while logically linking an employee with a department.
One problem with lookup fields, however, is that lookup fields, such as the department.sub.-- id field, make the database appear complex from a user's perspective. In particular, viewing and updating the database becomes more difficult when lookup fields are used. For example, as described above, if a user wanted to find out what department an employee worked in, the user would have to employ a two step process: first, the user would have to go to the employee table and find the employee the user was interested in; second, the user would have to use the department ID stored for that employee and then go to the department table to determine the name of the department associated with that department ID. Consequently, a user must preform two lookups in two separate tables in order to view all the relevant employee information. This might not be a problem if the user is familiar with the database and knows the commands to operate the database. However, many database users are not aware of the underlying structure of the database nor are they familiar with the database command language.
Updating a database that contains lookup fields also presents problems to a user who is not familiar with the structure of the database. For example, if a user of the database wants to update the database to reflect the fact that employee "John" has moved from the engineering department to the marketing department, the user would have to update the department.sub.-- id field in the employee table so that it contains the marketing id (i.e., 200) rather than the engineering id (i.e., 100). The user, therefore, would have to know the department identification for the marketing department. To determine the department id for the marketing department, the user would have to do a search of the Department table. Consequently, what appears to be a simple update actually requires two steps. The first step being a search of the department database, and the second step being an update of the employee table. Again, this might not be a problem if the user is familiar with the database and knows the commands to operate the database. However, as was stated above, many database users are not aware of the underlying structure of the database nor are they familiar with the database command language.
Another problem encountered with relational databases is that most tables contain a set of information that is of no value to certain users. For example, there are users who may be interested in accessing information concerning only a subset of all the employees. In particular, the manager of the Engineering department is only interested in accessing the Employee table to retrieve information concerning the employees that work in the engineering department; the remainder of the information in the Employee table having no value to the engineering manager. Consequently, if the engineering manager were to access the database and view the entire Employee table, the manager would be presented with more information than is needed, and the manager would have to spend valuable time sifting through the table to find what he is looking for.
Another problem encountered with relational databases is that any given table usually contains information that should not be accessed by certain users. For example, only a limited number of users should be given the capability to view and/or update salary information of an employee.
One solution to the problems described above is to create a database having several tables without any lookup fields, and then limit access to each table. For example, there could be a separate "Employee Salary" table that may be accessed only by a select group of users who have the authority to view employee salary information, or there could be a separate Employee table for each department within the company so that the manager of each respective department will only have access to the tables pertaining to their department. The disadvantage of using this approach is that it is less efficient, creates a complex database structure, and increases the difficulty in maintaining database integrity.
A second solution is to have the user learn and become an expert in the use of SQL (structured query language). SQL is the command language of relational database systems. When a user wants to retrieve or update information in a relational database, the user would create a SQL command to perform the task. If a user were expert in SQL, the user could select certain portions of a table to view. The drawback with this approach is the time and effort that is needed to learn SQL, and there would be nothing to stop a user from viewing/updating information that he is not allowed to view or update.
Another approach is to have a computer programmer design a custom application that will hide the intricacies of the database from the user. This approach is an improvement over having the user learn SQL. The disadvantage to this approach, however, is that changes to the structure of the database will necessitate changes to the custom application. Similarly, if a user's requirements change or a new group of users want their own view of the database, the custom application will have to be modified.
What is needed is a system that solves the problem caused by lookup fields, and allows a user to easily retrieve only those records and fields of a table that are of interest to the user, without requiring the user to invest time and effort in learning SQL, and without the user having to know the underlying structure of the database. Furthermore, the application must be flexible enough so that an administrator can easily create custom designed views for different groups of users who have different viewing and updating requirements of the same table. Lastly, changes to the database structure or changes in a user's requirements should not necessitate changes to the application.